﻿using System;
using System.Data;
using Dapper;
using System.Linq;
using Ayatta.Contract;
using Ayatta.Service.Extension;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class AccountService : BaseService, IAccountService
    {

        #region Account

        public Account AccountGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.Account)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<Account>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public PagedList<Account> AccountPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.Account)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<Account>(template);
            }
        }

        #endregion

        #region AccountDetailMoney

        public bool AccountDetailMoneyCreate(AccountDetailMoney o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountDetailMoney)
            .Values(o.Free, o.Flow, o.Group, o.UserId, o.Amount, o.MoneyPre, o.MoneyNow, o.MoneyFreePre, o.MoneyFreeNow, o.ForUser, o.ForUserId, o.ForUserName, o.ForeignKey, o.Subject, o.Message, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailMoneyUpdate(AccountDetailMoney o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.AccountDetailMoney)
            .Set("[Free]={0}", o.Free)
            .Set("[Flow]={0}", o.Flow)
            .Set("[Group]={0}", o.Group)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Amount]={0}", o.Amount)
            .Set("[MoneyPre]={0}", o.MoneyPre)
            .Set("[MoneyNow]={0}", o.MoneyNow)
            .Set("[MoneyFreePre]={0}", o.MoneyFreePre)
            .Set("[MoneyFreeNow]={0}", o.MoneyFreeNow)
            .Set("[ForUser]={0}", o.ForUser)
            .Set("[ForUserId]={0}", o.ForUserId)
            .Set("[ForUserName]={0}", o.ForUserName)
            .Set("[ForeignKey]={0}", o.ForeignKey)
            .Set("[Subject]={0}", o.Subject)
            .Set("[Message]={0}", o.Message)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailMoneyDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountDetailMoney)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountDetailMoney AccountDetailMoneyGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailMoney)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailMoney>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountDetailMoney> AccountDetailMoneyList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailMoney)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailMoney>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountDetailMoney> AccountDetailMoneyPagedList(int page = 1, int size = 20, int? userId = null, bool? flow = null, int? group = null, DateTime? beninDate = null, DateTime? endDate = null)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountDetailMoney)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountDetailMoney>(template);
            }
        }

        #endregion
    
        #region AccountDetailCoin

        public bool AccountDetailCoinCreate(AccountDetailCoin o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountDetailCoin)
            .Values(o.Free, o.Flow, o.Group, o.UserId, o.Amount, o.CoinPre, o.CoinNow, o.CoinFreePre, o.CoinFreeNow, o.ForUser, o.ForUserId, o.ForUserName, o.ForeignKey, o.Subject, o.Message, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailCoinUpdate(AccountDetailCoin o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.AccountDetailCoin)
            .Set("[Free]={0}", o.Free)
            .Set("[Flow]={0}", o.Flow)
            .Set("[Group]={0}", o.Group)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Amount]={0}", o.Amount)
            .Set("[CoinPre]={0}", o.CoinPre)
            .Set("[CoinNow]={0}", o.CoinNow)
            .Set("[CoinFreePre]={0}", o.CoinFreePre)
            .Set("[CoinFreeNow]={0}", o.CoinFreeNow)
            .Set("[ForUser]={0}", o.ForUser)
            .Set("[ForUserId]={0}", o.ForUserId)
            .Set("[ForUserName]={0}", o.ForUserName)
            .Set("[ForeignKey]={0}", o.ForeignKey)
            .Set("[Subject]={0}", o.Subject)
            .Set("[Message]={0}", o.Message)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailCoinDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountDetailCoin)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountDetailCoin AccountDetailCoinGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailCoin)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailCoin>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountDetailCoin> AccountDetailCoinList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailCoin)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailCoin>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountDetailCoin> AccountDetailCoinPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountDetailCoin)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountDetailCoin>(template);
            }
        }

        #endregion
    
        #region AccountDetailIntegral

        public bool AccountDetailIntegralCreate(AccountDetailIntegral o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountDetailIntegral)
            .Values(o.Flow, o.Group, o.UserId, o.Amount, o.IntegralPre, o.IntegralNow, o.ForeignKey, o.Subject, o.Message, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailIntegralUpdate(AccountDetailIntegral o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.AccountDetailIntegral)
            .Set("[Flow]={0}", o.Flow)
            .Set("[Group]={0}", o.Group)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Amount]={0}", o.Amount)
            .Set("[IntegralPre]={0}", o.IntegralPre)
            .Set("[IntegralNow]={0}", o.IntegralNow)
            .Set("[ForeignKey]={0}", o.ForeignKey)
            .Set("[Subject]={0}", o.Subject)
            .Set("[Message]={0}", o.Message)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailIntegralDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountDetailIntegral)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountDetailIntegral AccountDetailIntegralGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailIntegral)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailIntegral>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountDetailIntegral> AccountDetailIntegralList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailIntegral)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailIntegral>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountDetailIntegral> AccountDetailIntegralPagedList(int page = 1, int size = 50, int? userId=null, bool? flow = null, int? group = null)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountDetailIntegral)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountDetailIntegral>(template);
            }
        }

        #endregion
    
        #region AccountDetailGame

        public bool AccountDetailGameCreate(AccountDetailGame o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountDetailGame)
            .Values(o.Free, o.Group, o.UserId, o.GameId, o.ServerId, o.Amount, o.GamePre, o.GameNow, o.GameFreePre, o.GameFreeNow, o.ForeignKey, o.Subject, o.Message, o.ForUser, o.ForUserId, o.ForUserName, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailGameUpdate(AccountDetailGame o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.AccountDetailGame)
            .Set("[Free]={0}", o.Free)
            .Set("[Group]={0}", o.Group)
            .Set("[UserId]={0}", o.UserId)
            .Set("[GameId]={0}", o.GameId)
            .Set("[ServerId]={0}", o.ServerId)
            .Set("[Amount]={0}", o.Amount)
            .Set("[GamePre]={0}", o.GamePre)
            .Set("[GameNow]={0}", o.GameNow)
            .Set("[GameFreePre]={0}", o.GameFreePre)
            .Set("[GameFreeNow]={0}", o.GameFreeNow)
            .Set("[ForeignKey]={0}", o.ForeignKey)
            .Set("[Subject]={0}", o.Subject)
            .Set("[Message]={0}", o.Message)
            .Set("[ForUser]={0}", o.ForUser)
            .Set("[ForUserId]={0}", o.ForUserId)
            .Set("[ForUserName]={0}", o.ForUserName)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountDetailGameDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountDetailGame)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountDetailGame AccountDetailGameGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailGame)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailGame>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountDetailGame> AccountDetailGameList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountDetailGame)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountDetailGame>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountDetailGame> AccountDetailGamePagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountDetailGame)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountDetailGame>(template);
            }
        }

        #endregion

        #region RechargePackage

        public bool RechargePackageCreate(RechargePackage o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.RechargePackage)
            .Values(o.Title, o.SubTitle, o.Category, o.Icon, o.Image, o.Description, o.Amount, o.Original, o.MoneyGift, o.CoinGift, o.IntegralGift, o.BeginDate, o.EndDate, o.Usage, o.Priority, o.Limit, o.LimitTimes, o.Requirements, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool RechargePackageUpdate(RechargePackage o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.RechargePackage)
            .Set("[Title]={0}", o.Title)
            .Set("[SubTitle]={0}", o.SubTitle)
            .Set("[Category]={0}", o.Category)
            .Set("[Icon]={0}", o.Icon)
            .Set("[Image]={0}", o.Image)
            .Set("[Description]={0}", o.Description)
            .Set("[Amount]={0}", o.Amount)
            .Set("[Original]={0}", o.Original)
            .Set("[MoneyGift]={0}", o.MoneyGift)
            .Set("[CoinGift]={0}", o.CoinGift)
            .Set("[IntegralGift]={0}", o.IntegralGift)
            .Set("[BeginDate]={0}", o.BeginDate)
            .Set("[EndDate]={0}", o.EndDate)
            .Set("[Usage]={0}", o.Usage)
            .Set("[Priority]={0}", o.Priority)
            .Set("[Limit]={0}", o.Limit)
            .Set("[LimitTimes]={0}", o.LimitTimes)
            .Set("[Requirements]={0}", o.Requirements)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool RechargePackageDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.RechargePackage)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public RechargePackage RechargePackageGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.RechargePackage)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<RechargePackage>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<RechargePackage> RechargePackageList(bool? status = null)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.RechargePackage)
            .Where().If(status.HasValue, "[Status]={0}", status).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<RechargePackage>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<RechargePackage> RechargePackagePagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.RechargePackage)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<RechargePackage>(template);
            }
        }

        #endregion

        #region RechargePackageRequirement

        public bool RechargePackageRequirementCreate(RechargePackageRequirement o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.RechargePackageRequirement)
            .Values(o.Category, o.Title, o.Param, o.Description, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool RechargePackageRequirementUpdate(RechargePackageRequirement o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.RechargePackageRequirement)
            .Set("[Category]={0}", o.Category)
            .Set("[Title]={0}", o.Title)
            .Set("[Param]={0}", o.Param)
            .Set("[Description]={0}", o.Description)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool RechargePackageRequirementDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.RechargePackageRequirement)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public RechargePackageRequirement RechargePackageRequirementGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.RechargePackageRequirement)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<RechargePackageRequirement>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<RechargePackageRequirement> RechargePackageRequirementList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.RechargePackageRequirement)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<RechargePackageRequirement>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<RechargePackageRequirement> RechargePackageRequirementPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.RechargePackageRequirement)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<RechargePackageRequirement>(template);
            }
        }

        #endregion

        #region PaymentPlatform

        public bool PaymentPlatformCreate(PaymentPlatform o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.PaymentPlatform)
            .Values(o.Name, o.Image, o.MerchantId, o.PrivateKey, o.PublicKey, o.GatewayUrl, o.CallbackUrl, o.NotifyUrl, o.QueryUrl, o.RefundUrl, o.Description, o.Priority, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentPlatformUpdate(PaymentPlatform o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.PaymentPlatform)
            .Set("[Name]={0}", o.Name)
            .Set("[Image]={0}", o.Image)
            .Set("[MerchantId]={0}", o.MerchantId)
            .Set("[PrivateKey]={0}", o.PrivateKey)
            .Set("[PublicKey]={0}", o.PublicKey)
            .Set("[GatewayUrl]={0}", o.GatewayUrl)
            .Set("[CallbackUrl]={0}", o.CallbackUrl)
            .Set("[NotifyUrl]={0}", o.NotifyUrl)
            .Set("[QueryUrl]={0}", o.QueryUrl)
            .Set("[RefundUrl]={0}", o.RefundUrl)
            .Set("[Description]={0}", o.Description)
            .Set("[Priority]={0}", o.Priority)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentPlatformDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.PaymentPlatform)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public PaymentPlatform PaymentPlatformGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.PaymentPlatform)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<PaymentPlatform>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<PaymentPlatform> PaymentPlatformList(bool? status = null)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.PaymentPlatform)
            .Where().If(status.HasValue, "[Status]={0}", status).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<PaymentPlatform>(template.Sql, template.Param).ToList();
            }
        }

        #endregion

        #region PaymentBank

        public bool PaymentBankCreate(PaymentBank o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.PaymentBank)
            .Values(o.Name, o.Image, o.Description, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentBankUpdate(PaymentBank o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.PaymentBank)
            .Set("[Name]={0}", o.Name)
            .Set("[Image]={0}", o.Image)
            .Set("[Description]={0}", o.Description)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentBankDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.PaymentBank)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public PaymentBank PaymentBankGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.PaymentBank)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<PaymentBank>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<PaymentBank> PaymentBankList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.PaymentBank)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<PaymentBank>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<PaymentBank> PaymentBankPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.PaymentBank)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<PaymentBank>(template);
            }
        }

        #endregion

        #region PaymentEBank

        public bool PaymentEBankCreate(PaymentEBank o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.PaymentEBank)
            .Values(o.BankId, o.PlatformId, o.GatewayCode, o.Description, o.Priority, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentEBankUpdate(PaymentEBank o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.PaymentEBank)
            .Set("[BankId]={0}", o.BankId)
            .Set("[PlatformId]={0}", o.PlatformId)
            .Set("[GatewayCode]={0}", o.GatewayCode)
            .Set("[Description]={0}", o.Description)
            .Set("[Priority]={0}", o.Priority)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool PaymentEBankDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.PaymentEBank)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public PaymentEBank PaymentEBankGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.PaymentEBank)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<PaymentEBank>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<PaymentEBank> PaymentEBankList(bool rich = false, int? platformId = null)
        {
            if (!rich)
            {
                var template = new SqlBuilder().Select("*")
                    .From(Database.Account.Table.PaymentEBank)
                    .Where().If(platformId.HasValue, "[PlatformId]={0}", platformId)
                    .Complete();

                using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
                {
                    return connection.Query<PaymentEBank>(template.Sql, template.Param).ToList();
                }
            }
            else
            {
                var template = new SqlBuilder().Select("*")
                .From(Database.Account.Table.PaymentEBank + " a")
                .InnerJoin(Database.Account.Table.PaymentPlatform + " b on a.PlatformId=b.Id")
                .InnerJoin(Database.Account.Table.PaymentBank + " c on a.BankId=c.Id")
                .Where("a.[Status]=1 and b.[Status]=1 and c.[Status]=1")
                .If(platformId.HasValue, "a.[PlatformId]={0}", platformId)
                .Complete();

                using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
                {
                    return connection.Query<PaymentEBank, PaymentPlatform, PaymentBank>(template.Sql, template.Param).ToList();
                }
            }
        }

        public PagedList<PaymentEBank> PaymentEBankPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.PaymentEBank)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<PaymentEBank>(template);
            }
        }

        #endregion

        #region AccountRecharge

        public bool AccountRechargeCreate(AccountRecharge o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountRecharge)
            .Values(o.Id, o.UserId, o.Category, o.PackageId, o.Amount, o.Discount, o.Total, o.Gateway, o.ForUser, o.ForUserId, o.ForUserName, o.GameId, o.ServerId, o.ForeignKey, o.Extra, o.Status, o.SubStatus,o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountRechargeUpdate(AccountRecharge o)
        {
            var template = new SqlBuilder()
            .Update(Database.Account.Table.AccountRecharge)
            .Set("[UserId]={0}", o.UserId)
            .Set("[Category]={0}", o.Category)
            .Set("[PackageId]={0}", o.PackageId)
            .Set("[Amount]={0}", o.Amount)
            .Set("[Discount]={0}", o.Discount)
            .Set("[Total]={0}", o.Total)
            .Set("[Gateway]={0}", o.Gateway)
            .Set("[ForUser]={0}", o.ForUser)
            .Set("[ForUserId]={0}", o.ForUserId)
            .Set("[ForUserName]={0}", o.ForUserName)
            .Set("[GameId]={0}", o.GameId)
            .Set("[ServerId]={0}", o.ServerId)
            .Set("[ForeignKey]={0}", o.ForeignKey)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Set("[SubStatus]={0}", o.SubStatus)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AccountRechargeDelete(string id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountRecharge)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountRecharge AccountRechargeGet(string id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountRecharge)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountRecharge>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountRecharge> AccountRechargeList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountRecharge)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountRecharge>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountRecharge> AccountRechargePagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountRecharge)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountRecharge>(template);
            }
        }

        public int AccountRechargeCountGet(int userId, int? packageId = null, byte? category = null, bool? status = null)
        {
            var builder = new RegexSqlBuilder()
                .Table(Database.Account.Table.AccountRecharge)
                .Where("[UserId]=@UserId", new { userId });
            if (packageId.HasValue)
            {
                builder.Where("[PackageId]=@PackageId", new { packageId });
            }
            if (category.HasValue)
            {
                builder.Where("[Category]=@Category", new { category });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            var template = builder.Complete(RegexSqlTemplate.Count);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<int>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        #endregion

        #region AccountPayment

        public bool AccountPaymentCreate(AccountPayment payment)
        {
            var template =new SqlBuilder().Insert(Database.Account.Table.AccountPayment)
                    .Values(payment.Id, payment.PayNo, payment.UserId, payment.Category, payment.SubCategory,payment.ForeignKey, payment.Amount, payment.Balance, payment.Method, payment.EBankId,payment.PlatformId, payment.Gateway, payment.BankCard, payment.BankName, payment.CardNo,payment.CardPwd, payment.CardAmount, payment.IpAddress, payment.ExternalNo, payment.Flag,payment.Extra, payment.Status,payment.PaidOn,payment.CreatedBy, payment.CreatedOn)
                    .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                  return   connection.Execute(template.Sql, template.Param)==1;
            }
        }

        public bool AccountPaymentRechargeCreate(AccountRecharge recharge, AccountPayment payment)
        {
            var a = new SqlBuilder().Insert(Database.Account.Table.AccountRecharge)
                .Values(recharge.Id, recharge.UserId, recharge.Category, recharge.PackageId, recharge.Amount, recharge.Discount, recharge.Total, recharge.Gateway, recharge.ForUser, recharge.ForUserId, recharge.ForUserName, recharge.GameId, recharge.ServerId, recharge.ForeignKey, recharge.Extra, recharge.Status, recharge.SubStatus, recharge.CreatedBy, recharge.CreatedOn);

            var b = new SqlBuilder().Insert(Database.Account.Table.AccountPayment)
            .Values(payment.Id, payment.PayNo, payment.UserId, payment.Category, payment.SubCategory, payment.ForeignKey, payment.Amount, payment.Balance, payment.Method, payment.EBankId, payment.PlatformId, payment.Gateway, payment.BankCard, payment.BankName, payment.CardNo, payment.CardPwd, payment.CardAmount, payment.IpAddress, payment.ExternalNo, payment.Flag, payment.Extra, payment.Status, payment.PaidOn, payment.CreatedBy, payment.CreatedOn);

            var template = new SqlBuilder().Append(a).Append(b).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    connection.Execute(template.Sql, template.Param, transaction);
                    transaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    return false;
                }
            }
        }

        public bool AccountPaymentRechargeStatusUpdate(string paymentId, string rechargeId, DateTime paidOn, string externalNo)
        {
            var a = new SqlBuilder()
                .Update(Database.Account.Table.AccountPayment)
                .Set("[Status]=1,[ExternalNo]={0}", externalNo)
                .Where("[Id]={0} AND [ForeignKey]={1}", paymentId, rechargeId);
            var b = new SqlBuilder()
                .Update(Database.Account.Table.AccountRecharge)
                .Set("[Status]=1,PaidOn={0}", paidOn)
                .Where("[Id]={0}", rechargeId);

            var template = new SqlBuilder().Append(a).Append(b).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    connection.Execute(template.Sql, template.Param, transaction);
                    transaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        public bool AccountPaymentOrderPayByEBank(string id, DateTime paidOn, string externalNo)
        {
            var param = new {id, paidOn, externalNo};
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<bool>("[Proc.Account.Payment.Order.Pay.By.EBank]", param, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
        }

        public bool AccountPaymentDelete(string id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Account.Table.AccountPayment)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountPayment AccountPaymentGet(string id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountPayment)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountPayment>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<AccountPayment> AccountPaymentList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountPayment)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountPayment>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<AccountPayment> AccountPaymentPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountPayment)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountPayment>(template);
            }
        }

        #endregion

        #region AccountPaymentLog

        public bool AccountPaymentLogCreate(AccountPaymentLog o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Account.Table.AccountPaymentLog)
            .Values(o.PayId, o.PayNo, o.UserId, o.Subject, o.Message, o.Priority, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public AccountPaymentLog AccountPaymentLogGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Account.Table.AccountPaymentLog)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.Query<AccountPaymentLog>(template.Sql, template.Param).FirstOrDefault();
            }
        }


        public PagedList<AccountPaymentLog> AccountPaymentLogPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Account.Table.AccountPaymentLog)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Account).EnsureOpen())
            {
                return connection.PagedList<AccountPaymentLog>(template);
            }
        }

        #endregion

        #region IService
        public void CachePurge()
        {
            PurgeCache();
        }

        public PagedList<Magic<string, string, int>> CacheGet(int page = 1, int size = 50, int hitCount = 0)
        {
            return GetPagedCache(page, size, hitCount);
        }
        #endregion
    }
}
